SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION [dbo].[fnObtenerNormasDeContinuidad] (@idServicioAgente INT)  
RETURNS VARCHAR(25)   
AS
BEGIN 

DECLARE @NormaContinuidad VARCHAR(9)
DECLARE @Retorno VARCHAR(25)
SELECT @Retorno = '', @NormaContinuidad = ''

DECLARE Normas CURSOR FOR 
SELECT CONVERT(VARCHAR(9),N.Descripcion)
FROM tb_ColaDeMovimientos C 
INNER JOIN tb_PlazasDeMovimiento P ON P.IdCola = C.IdCola
INNER JOIN tb_Normas N ON C.NormaContinuidad = IdNorma
INNER JOIN tb_SubServiciosAgentes SS ON SS.IdSubServicio = P.Servicio
INNER JOIN tb_ServiciosAgentes S ON S.IdServicioAgente = SS.ServicioAgente
WHERE IdServicioAgente = @IdServicioAgente

OPEN Normas
FETCH NEXT FROM Normas INTO @NormaContinuidad

WHILE @@FETCH_STATUS = 0
BEGIN
	SELECT @Retorno = @Retorno + ' - [C]' + @NormaContinuidad
	FETCH NEXT FROM Normas INTO @NormaContinuidad
END

CLOSE Normas
DEALLOCATE Normas

RETURN(RIGHT(@Retorno, LEN(@Retorno) - 3))

END

GO
GRANT EXECUTE ON  [dbo].[fnObtenerNormasDeContinuidad] TO [SoloVer]
GO
